package com.gdk.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eclipse.jdt.internal.compiler.codegen.ExceptionLabel;


public class importExcelTest {
	public static void main(String[] args) {
		Map<String,Object> result_map = new HashMap<String,Object>();
	 	
		result_map.put("result_status", 0);
	 	try {
			String file_address = "C:/Users/91578/Desktop/user_template.xlsx";
			
			System.out.println("file_address  : "+file_address);
			if(!file_address.contains(".")) {
				result_map.put("result_status", -3);
				
			}
			
			int indexOf = file_address.lastIndexOf(".");
			
			System.out.println("indexOf :"+indexOf);
			System.out.println("file_address.length() :"+file_address.length());
			
			if(indexOf+1 == file_address.length()) {
				result_map.put("result_status", -4);
			}
			
			
			String file_type = file_address.substring(indexOf+1, file_address.length());
			System.out.println("file_type : "+file_type);
			
			if(!file_type.equals("xlsx")) {
				result_map.put("result_status", -5);
			}
			
			//本地文件
			XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(file_address)));
			//远程文件
			//URL url = new URL(file_address);//把远程文件地址转换成URL格式
           // InputStream fin = url.openStream();
           // XSSFWorkbook workbook = new XSSFWorkbook(fin);
			
            
			 XSSFSheet sheet = null;
			 
			 List<Map<String,Object>> excelList = new ArrayList<Map<String,Object>>();
			 
	        System.out.println("表数量 :  "+ workbook.getNumberOfSheets());
	        //表
	        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
	        	
	            sheet = workbook.getSheetAt(i);
	            System.out.println("表 : "+i+1+"  数据行 :  "+ sheet.getPhysicalNumberOfRows());
	            int flag = 0;
	            //行
	            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
	            	Map<String,Object> rowMap = new HashMap<String,Object>();
	            	if(flag == 1) {break;}
	            	System.out.println("行数   :  "+(j+1));
	                XSSFRow row = sheet.getRow(j);
	                System.out.println("row  : "+row.getLastCellNum());
	                
	                //判断第一行每列是否有数据
	                if((j+1) == 1) {
	            		System.out.println("11 : "+row.getCell(0) + "  "+row.getCell(1) +"   "+row.getCell(2) +"   "+row.getCell(3)+"   "+row.getCell(4)+"   "+row.getCell(5) );
	            		if(row.getCell(0) == null || "".equals(row.getCell(0)) || row.getCell(1) == null || "".equals(row.getCell(1)) || row.getCell(2) == null || "".equals(row.getCell(2))  || row.getCell(3) == null || "".equals(row.getCell(3)) || row.getCell(4) == null || "".equals(row.getCell(4))  || row.getCell(5) == null || "".equals(row.getCell(5))) {
	            			System.out.println("文件内容错误");
	        				result_map.put("result_status", -6);
	        				flag = 1;
	        				break;
	            		}
            		}
	                
	                //row.getLastCellNum()  代表有几列数据
	                //列
	                for (int k = 0; k < row.getLastCellNum(); k++) {
	                	//判断第一行每列是否符合模板数据
	                	if((j+1) == 1) {
	                		if(!row.getCell(0).toString().trim().equals("姓名")) {
	                			System.out.println("文件内容错误 : "+row.getCell(0));
	            				result_map.put("result_status", -7);
	            				flag = 1;
	            				break;
	                		}
	                		if(!row.getCell(1).toString().trim().equals("手机号")) {
	                			System.out.println("文件内容错误 : "+row.getCell(1));
	            				result_map.put("result_status", -7);
	            				flag = 1;
	            				break;
	                		}
	                		if(!row.getCell(2).toString().trim().equals("身份证")) {
	                			System.out.println("文件内容错误 : "+row.getCell(2));
	            				result_map.put("result_status", -7);
	            				flag = 1;
	            				break;
	                		}
	                		if(!row.getCell(3).toString().trim().equals("座次")) {
	                			System.out.println("文件内容错误 : "+row.getCell(3));
	            				result_map.put("result_status", -7);
	            				flag = 1;
	            				break;
	                		}
	                		if(!row.getCell(4).toString().trim().equals("性别")) {
	                			System.out.println("文件内容错误 : "+row.getCell(4));
	            				result_map.put("result_status", -7);
	            				flag = 1;
	            				break;
	                		}
	                		if(!row.getCell(5).toString().trim().equals("类型")) {
	                			System.out.println("文件内容错误 : "+row.getCell(5));
	            				result_map.put("result_status", -7);
	            				flag = 1;
	            				break;
	                		}
	                		
	                	}
	                	//打印每一列的数据
	                	//System.out.println(row.getCell(k));
	                	DecimalFormat df = new DecimalFormat("#.#");
	                	//System.out.println(df.format(a));
	                	if((j+1) > 1) {
	                		//获取每一列数据
	                		if(row.getCell(k) != null && !"".equals(row.getCell(k).toString().trim())) {
	                			if(row.getCell(0) != null && !"".equals(row.getCell(0).toString().trim())) {
		                			if(!row.getCell(0).toString().trim().equals("null")) {
		                				String u_name = row.getCell(0).toString().trim();
		                				rowMap.put("u_name", u_name);
		                				
		                				if(row.getCell(1) != null) {
		                					if(!row.getCell(1).toString().trim().equals("null")) {
				                				String u_phone = row.getCell(1).toString().trim();
				                				
				                				if(u_phone.contains(".0")) {
				                					int f = u_phone.lastIndexOf(".0");
				                					u_phone = u_phone.substring(0,f);
				                				}
				                			
				                				
				                				rowMap.put("u_phone", u_phone);
				                			}
		                				}
		                				if(row.getCell(2) != null) {
		                					if(!row.getCell(2).toString().trim().equals("null")) {
				                				String u_idc = row.getCell(2).toString().trim();
				                				if(u_idc.contains(".0")) {
				                					int f = u_idc.lastIndexOf(".0");
				                					u_idc = u_idc.substring(0,f);
				                				}
				                				rowMap.put("u_idc", u_idc.toString());
				                			}
		                				}
		                				if(row.getCell(3) != null) {
			                				if(!row.getCell(3).toString().trim().equals("null")) {
				                				String u_zc = row.getCell(3).toString().trim();
				                				if(u_zc.contains(".0")) {
				                					int f = u_zc.lastIndexOf(".0");
				                					u_zc = u_zc.substring(0,f);
				                				}
				                				rowMap.put("u_zc", u_zc);
				                			}
		                				}
		                				if(row.getCell(4) != null) {
			                				if(!row.getCell(4).toString().trim().equals("null")) {
				                				String p_sex = row.getCell(4).toString().trim();
				                				if(p_sex.equals("女")) {
				                					rowMap.put("u_sex", 2);
				                				}else {
				                					rowMap.put("u_sex", 1);
				                				}
				                			}
		                				}else {
		                					rowMap.put("u_sex", 1);
		                				}
		                				if(row.getCell(5) != null) {
			                				if(!row.getCell(5).toString().trim().equals("null")) {
				                				String p_sex = row.getCell(5).toString().trim();
				                				if(p_sex.equals("销售")) {
				                					rowMap.put("u_type", 2);
				                				}else {
				                					rowMap.put("u_type", 1);
				                				}
				                			}
		                				}else {
		                					rowMap.put("u_type", 1);
		                				}
		                			}
	                			}
	                		}
	                		
	                	}
	                	
	                }
	                System.out.println("rowMap : "+rowMap);
	                //不为空
	                if(!rowMap.isEmpty()) {
	                	excelList.add(rowMap);	                	
	                }
	              //  System.out.println();
	            }
	            
                //System.out.print(row.getCell(k) + "\t");
	        }
	        System.out.println("excelList : "+excelList);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			System.out.println("文件路径错误");
			result_map.put("result_status", -1);
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("IO错误");
			result_map.put("result_status", -2);
		}
		 
	 	 System.out.println("result_map : "+result_map);
	}
	
	//判断字符串是否数字领
	public static boolean isNumeric(String str){

	    Pattern pattern = Pattern.compile("[0-9]*");

	    return pattern.matcher(str).matches();   

	 }
}
